﻿Imports System.Data.Linq
Imports System.Linq
Public Class MDebit
    Private db As New TanNamChinhDataContext
    Public Function GetSellingFeeOfFile(ByVal idfile As Integer, ByVal idHBL As Integer) As IQueryable
        Dim query = From b In db.tblSellingFees Join c In db.tblHBLExchanges On b.ID_HBL Equals c.ID_HBL And b.ID_Currency Equals c.ID_Currency
                    Where b.ID_HBL = idHBL And b.tblHBL.tblFileMBL.ID_File = idfile And b.UnitPrice > 0
                    Order By b.tblFee.IsBill, b.tblFee.Rank Ascending
                    Select New With {
                        .FeeName = If(b.tblFee IsNot Nothing, b.tblFee.FeeName, String.Empty),
                        .Currency = If(b.tblCurrency IsNot Nothing, b.tblCurrency.Code, String.Empty),
                        .Quantity = b.Quantity,
                        .UnitPrice = b.UnitPrice,
                        .Ratio = c.Ratio,
                        .Price = b.Price * c.Ratio,
                        .Description = b.Description}
        Return query
    End Function

    'Lấy thông tin File chưa ký hợp đồng

    Public Function getContList(ByVal idFile As Integer) As IQueryable
        Try
            Dim query = From b In db.tblConts
                        Where b.tblHBL.tblFileMBL.ID_File = idFile
                        Select b.tblContType.ID_ContType


            'Return (From r In db.tblCurrencies
            '           Select r).Single
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function getFileFeeList(ByVal idFile As Integer) As IQueryable
        Try

            ''Lấy các phí đã có trong filefee
            'Dim listFee = From b In db.tblFileFees
            '              Where b.tblFile.ID_File = idFile And b.IsBuying = True
            '              Select b.ID_Fee


            'Lấy các phí mới, có trong fee, chưa có trong filefee
            Dim listnewFee = From c In db.tblFees
                             Where c.ID_FeeType = 1 And Not (From b In db.tblFileFees
                                                              Where b.tblFile.ID_File = idFile
                                                              Select b.ID_Fee).Contains(c.ID_Fee)
                            Select c.ID_Fee

            'Thêm phí mới vào filefee rồi load lại

            If listnewFee IsNot Nothing Then
                'Add phí vào
                For Each idFee As Integer In listnewFee
                    Dim obj As New tblFileFee
                    obj.ID_Fee = idFee
                    obj.ID_File = idFile
                    InsertNewRecord(obj)
                Next

            End If

            'Lấy các thông tin fee buying theo từng file
            Dim query = From b In db.tblFileFees
                        Where b.tblFile.ID_File = idFile
                        Select
                        New With {b.ID_FileFee, b.ID_Fee, b.tblFee.FeeName, b.UnitPrice, b.Quantity, b.Price, b.InvoiceNo, b.Description, b.ID_Currency}
            Return query
        Catch ex As Exception
            Throw ex
        End Try
    End Function
    Public Function ExistFileFee(ByVal idFile As Integer, ByVal idFeeType As Integer) As Boolean
        Dim numberFileFee As Integer
        numberFileFee = (From b In db.tblFileFees
                Where b.ID_File = idFile And b.tblFee.tblFeeType.ID_FeeType = idFeeType
                Select b).Count
        Dim Fee As Integer
        Fee = (From b In db.tblFees
               Where b.ID_FeeType = idFeeType
               Select b).Count()

        If numberFileFee > 0 Then
            Return True
        Else
            Return False

        End If

    End Function

    Public Function getBuyingFollowCont(ByVal idFile As Integer, ByVal idmbl As Integer, ByVal idVoyage As Integer) As ISingleResult(Of TNCDAL.GetBuyingRateOfFileResult)
        Dim query = db.GetBuyingRateOfFile(idFile, idVoyage, idmbl, 1)
        '    var query =
        'from c in dc.Customers
        'where !(from o in dc.Orders
        '        select o.CustomerID)
        '    .Contains(c.CustomerID)
        'select c;
        'Dim query2 As ISingleResult(Of TNCDAL.GetBuyingRateOfFileResult) = From f In db.tblFees
        '             Where Not (From o In query
        '                       Select o.ID_Fee).Contains(f.ID_Fee)
        '                   Select f.ID_Fee, f.FeeName, Price = 0.0

        'Dim emptyTable As tblTruck() = {New tblTruck With {.ID_Truck = 0, .TruckName = "-------"}}
        ' Dim q = (From e In query2 Select e).Union(query).ToList
        Return query
    End Function
    Public Sub InsertNewRecord(ByVal obj As tblFileFee)
        'db.tblCurrencies.InsertOnSubmit(obj)
        db.GetTable(Of tblFileFee)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub
    Public Sub InsertNewRecord(ByVal obj As tblContFee)
        'db.tblCurrencies.InsertOnSubmit(obj)
        db.GetTable(Of tblContFee)().InsertOnSubmit(obj)
        db.SubmitChanges()
    End Sub

    Public Sub UpdateRecord(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_FileFee = obj.ID_FileFee
                                Select b).Single()
        getData.ID_File = obj.ID_File
        getData.ID_Fee = obj.ID_Fee
        getData.Price = obj.Price
        getData.InvoiceNo = obj.InvoiceNo
        getData.UnitPrice = obj.UnitPrice
        getData.Quantity = obj.Quantity
        getData.Description = obj.Description
        getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.ID_PaymentVoucher = obj.ID_PaymentVoucher

        getData.ID_Currency = obj.ID_Currency
        db.SubmitChanges()

    End Sub
    Public Sub UpdateRecord(ByVal obj As tblContFee)
        Dim getData = (From b In db.tblContFees
                                Where b.ID_ContFee = obj.ID_ContFee
                                Select b).Single()

        getData.Price = obj.Price


        db.SubmitChanges()

    End Sub
    Public Sub UpdatePaymentVoucher(ByVal obj As tblFileFee)
        Dim getData = (From b In db.tblFileFees
                                Where b.ID_FileFee = obj.ID_FileFee
                                Select b).Single()
        'getData.ID_File = obj.ID_File
        'getData.ID_Fee = obj.ID_Fee
        'getData.Price = obj.Price
        'getData.InvoiceNo = obj.InvoiceNo
        'getData.ID_AdvanceVoucher = obj.ID_AdvanceVoucher
        getData.ID_PaymentVoucher = obj.ID_PaymentVoucher
        'getData.ID_ReceiptVoucher = obj.ID_ReceiptVoucher

        db.SubmitChanges()

    End Sub
    Public Sub DeleteRecord(ByVal id As Integer)
        Dim getData = (From b In db.tblFiles
                                Where b.ID_File = id
                                Select b).Single()
        db.tblFiles.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Sub DeleteBuyingFee(ByVal idfile As Integer)
        Dim getData = (From b In db.tblFiles
                                Where b.ID_File = idfile
                                Select b).Single()
        db.tblFiles.DeleteOnSubmit(getData)
        db.SubmitChanges()
    End Sub
    Public Function getNumberofCont(ByVal idFile As Integer) As Integer
        Dim num As Integer
        Try
            num = (From b In db.tblConts
                      Where b.tblHBL.tblFileMBL.ID_File = idFile
                        Select b
                       ).Count
        Catch ex As Exception
            Throw ex
        End Try
        Return num
    End Function
    Public Function getPriceFollowFee(ByVal idFile As Integer, ByVal idFee As Integer) As Double
        Dim nums As Double
        Try
            nums = (From b In db.tblSellingFees
                   Where b.tblHBL.tblFileMBL.ID_File = idFile And b.ID_Fee = idFee
                   Select b.Price).First()
        Catch ex As Exception
            Return 0
        End Try
        Return nums
    End Function
    Public Function getPriceFollowFee(ByVal idFileFee As Integer) As Double
        Dim nums As Double
        Try
            nums = (From b In db.tblFileFees
                   Where b.ID_FileFee = idFileFee
                   Select b.Price).First()
        Catch ex As Exception
            Return 0
        End Try
        Return nums
    End Function
    ''' <summary>
    ''' Kiểm tra xem phí đã được tạm ứng chưa
    '''  Trả về true là có tồn tại, false là chưa có
    ''' </summary>
    ''' <param name="id_filecont"></param>
    ''' <param name="id_fee"></param>
    ''' <returns> </returns>
    ''' <remarks></remarks>
    Public Function IsExistFee(ByVal id_filecont As Integer, ByVal id_fee As Integer, ByVal isfile As Boolean) As Boolean
        Dim query As IQueryable

        If isfile Then
            'Nếu đang xét filefee

            query = From a In db.tblFileFees
                    Where a.ID_Fee = id_fee And a.ID_File = id_filecont
                    Select a



            Dim count As Integer = 0
            For Each obj As tblFileFee In query
                count += 1
            Next

            If count <> 0 Then
                Return True
            Else
                Return False
            End If

        Else
            'Nếu là contfee

            query = From a In db.tblContFees
                  Where a.ID_Fee = id_fee And a.ID_Cont = id_filecont
                  Select a



            Dim count As Integer = 0
            For Each obj As tblContFee In query
                count += 1
            Next

            If count <> 0 Then
                Return True
            Else
                Return False
            End If
        End If



    End Function
#Region "Thanh"
    Public Function GetBuyingRateList(ByVal idFile As Integer) As ISingleResult(Of sp_Thanh_GetBuyingRateResult)
        Try
            Return db.sp_Thanh_GetBuyingRate(idFile)
        Catch ex As Exception
            Return Nothing
        End Try
    End Function
#End Region
End Class
